﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_BackDocument_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_BackDocument_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_BackDocument_V2]
    @DocID int
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

IF ( @@TRANCOUNT = 0 )
BEGIN
    RAISERROR(N'This sproc must be executed within an active transaction.', 16, 1);
    RETURN -1;
END

DECLARE @order_id   INT;
DECLARE @step_id    INT;
DECLARE @flow_id    INT;
DECLARE @msgid      INT;
DECLARE @staffname  nvarchar(100);

SET @staffname = ''
SELECT @order_id= ISNULL(MAX(ORDER_ID), 0) FROM dbo.UDS_FLOW_PATH WHERE DOC_ID = @docid;

IF @order_id > 1
BEGIN
    --删除上次提交人员备份
    DELETE FROM dbo.uds_flow_path WHERE doc_id = @docid and order_id = @order_id;

    --删除上次人员
    DELETE FROM dbo.uds_flow_status WHERE doc_id = @docid;

    --恢复上次提交人员
    INSERT INTO dbo.uds_flow_status (doc_id,staff_id,status)
        SELECT @docid,staff_id,0
        FROM dbo.uds_flow_path
        WHERE doc_id = @docid and order_id = @order_id - 1;

    --获得当前流程和步骤
    SELECT @flow_id = flow_id, @step_id = step_id FROM dbo.uds_flow_path WHERE doc_id = @docid and order_id = @order_id - 1;

    --获得最后一个回退人姓名
    SELECT TOP 1 @staffname = b.realname
        FROM
            dbo.uds_flow_postil a,
            dbo.uds_staff b
        WHERE
            a.postiler_id = b.staff_id
            and a.doc_id = @docid
            and a.postil_type = 2
        ORDER BY
            postil_date DESC;

    --回退提醒
    INSERT INTO dbo.uds_sms_msg (sender,content,sendtime,type) VALUES(@staffname,'有新公文被退回！',getdate(), 1);
    SET @msgid = @@IDENTITY;

    INSERT INTO dbo.uds_sms_receiver
        (msgid,receiver,mobileno,type,isread)
        SELECT
                @msgid, b.staff_name, b.mobile, 1, 0
            FROM 
                dbo.uds_flow_path a,
                dbo.uds_staff b
            WHERE
                a.staff_id = b.staff_id
                and a.doc_id = @docid
                and a.order_id = @order_id - 1;

    --删除真正的消息
    DELETE FROM dbo.UDS_SMS_MobileMsgSendBuffer
        WHERE msgid in (
            SELECT msgid
                FROM
                    dbo.uds_flow_message
                WHERE
                    doc_id = @docid
                    and flow_id = @flow_id
                    and step_id = @step_id
        );
        
    --删除当前步骤的提醒
    DELETE FROM dbo.uds_flow_message
        WHERE
            doc_id = @docid
            and flow_id = @flow_id
            and step_id = @step_id            

    --更新文档状态
    UPDATE dbo.uds_flow_document
        SET
            step_id = @step_id,
            flow_id = @flow_id,
            doc_status = 0
        WHERE
            DOC_ID = @docid;

    IF @step_id = 1
    BEGIN
        UPDATE dbo.uds_flow_document
            SET IsRunning = 0
            WHERE DOC_ID = @docid;
    END
END

END
GO
